<?php
/**
 * modele user, gestion du login admin et membre (session valable 7 jours)
 * @package PWE.modele
 * @author Gmax <gmaxfr78@gmail.com>
 */
class modele_user extends modele {
    public static $_table = 'user';
    public $type = 'user';
    
    private $_id;
    private $_nom;
    private $_prenom;
    private $_sexe;
    private $_naissance;
    private $_email;
    private $_phone1;
    private $_phone2;
    private $_mdp;
    private $_is_verif = 0;
    private $_is_newsletter = 1;
    private $_date_crea;
    
    private $_user_level_id;
    private $_user_level_nom;
    private $_user_level;
    
    private $_media_id = 0;
    private $_media = null;
    
    private $_adresse_id = 0;
    private $_adresse = null;
    
    private $_is_banned = 0;
    private $_is_deleted = 0;
    
    function __construct($id=null){
        if($id!==null){
            $this->_id = $id;
            $this->_constructObject();
        }else{
            $this->_id = 0;
        }
    }
    
    private function _constructObject(){
        $sqlQuery = 'SELECT ';
        $sqlQuery .= 't.*, ';
        $sqlQuery .= 'ul.nom AS user_level_nom, ';
        $sqlQuery .= 'ul.level AS user_level ';
        $sqlQuery .= 'FROM '.self::getTable().' AS t ';
        $sqlQuery .= 'LEFT JOIN '.PWE::getPrefixBdd().'user_level AS ul ON ul.id = t.user_level_id ';
        $sqlQuery .= 'WHERE t.id='.$this->_id.' ';
        $sqlQuery .= 'LIMIT 1';
        $datas = $this->_getOneResult($sqlQuery);
        if($datas){
            $this->_id = $datas['id'];
            $this->_nom =  $this->outputFormat($datas['nom']);
            $this->_prenom =  $this->outputFormat($datas['prenom']);
            $this->_sexe =  $datas['sexe'];
            $this->_naissance = $datas['naissance'];
            $this->_email = $datas['email'];
            $this->_phone1 = $datas['phone1'];
            $this->_phone2 = $datas['phone2'];
            $this->_mdp = $datas['mdp'];
            $this->_date_crea = $datas['date_crea'];
            $this->_is_verif = $datas['is_verif'];
            $this->_is_newsletter = $datas['is_newsletter'];
            $this->_user_level_id = $datas['user_level_id'];
            $this->_user_level_nom = $datas['user_level_nom'];
            $this->_user_level = $datas['user_level'];
            $this->_adresse_id = $datas['adresse_id'];
            $this->_media_id = $datas['media_id'];
            $this->_is_banned = $datas['is_banned'];
            $this->_is_deleted = $datas['is_deleted'];
        }else{
            $this->_id = 0;
        }
    }
    public function setId($id){
      $this->_id = $id;
    }
    public function getId(){
        return $this->_id;
    }
    public function getNom($input=false){
        if($input)return $this->inputFormat($this->_nom);
        else return $this->outputFormat($this->_nom);
    }
    public function getPrenom($input=false){
        if($input)return $this->inputFormat($this->_prenom);
        else return $this->outputFormat($this->_prenom);
    }
    /**
     * sexe 
     * h: homme
     * f: femme
     * @return string
     */
    public function getSexe(){
        return $this->_sexe;
    }
    /**
     * retourne l'age
     * @return int
     */
    public function getAge(){
        $arr1 = explode('/', tools_convert::date($this->getNaissance()));
        $arr2 = explode('/', date('d/m/Y'));
		
        if(($arr1[1] < $arr2[1]) || (($arr1[1] == $arr2[1]) && ($arr1[0] <= $arr2[0]))){
            return $arr2[2] - $arr1[2];
        }else{
            return $arr2[2] - $arr1[2] - 1;
        }
    }
    
    public function getNaissance(){
        return $this->_naissance;
    }
    public function getEmail(){
        return $this->_email;
    }
    public function getPhone1(){
        return $this->_phone1;
    }
    public function getPhone2(){
        return $this->_phone2;
    }
    public function getPassword(){
        return $this->_mdp;
    }
    public function getDateCrea(){
        return $this->_date_crea;
    }
    public function confirmEmail(){
        $this->_is_verif = 1;
        $sql = 'UPDATE '.self::getTable().' SET is_verif=1 WHERE id='.$this->getId();
        bdd_sql::doSql($sql);
        return true;
    }
    public function isVerif(){
        return ($this->_is_verif==1)?true:false;
    }
    public function isNewsletter(){
        return ($this->_is_newsletter==1)?true:false;
    }
    public function isBannded(){
        $sql = 'SELECT is_banned,is_deleted FROM '.self::getTable().' WHERE id='.$this->getId();
        $result = bdd_sql::doSql($sql,true);
        if($result['count']==1){
            $this->_is_banned = $result['results'][0]['is_banned'];
            if($result['results'][0]['is_deleted']==1)$this->_is_banned = 1;
        }
        return ($this->_is_banned==1)?true:false;
    }
    /**
     * bannir un membre
     * @return array
     */
    public function banned() {
        $this->_is_banned = 1;
        $sqlBan = 'UPDATE '.self::getTable().' SET ';
        $sqlBan .= 'is_banned='.$this->_is_banned.' ';
        $sqlBan .= 'WHERE id='.$this->_id.' LIMIT 1';
        
        bdd_sql::doSql($sqlBan,false);
        return array('success'=>true,'datas'=>array());
    }
    /**
     * réactiver le compte
     * @return array
     */
    public function unbanned() {
        $this->_is_banned = 0;
        $sqlBan = 'UPDATE '.self::getTable().' SET ';
        $sqlBan .= 'is_banned='.$this->_is_banned.' ';
        $sqlBan .= 'WHERE id='.$this->_id.' LIMIT 1';
        
        bdd_sql::doSql($sqlBan,false);
        return array('success'=>true,'datas'=>array());
    }
    
     /**
     * valeur numérique du niveau utilisateur :
     * 0 => super administrateur
     * 1 => administrateur
     * 2 => partenaire
     * 3 => membre
     * @return int
     */
    public function getLevel(){
        return $this->_user_level;
    }
    /**
     * identifiant du level utilisateur
     * @return int
     */
    public function getLevelId(){
        return $this->_user_level_id;
    }
    /**
     * renvoi le nom du niveau de l'utilisateur
     * @param boolean $input
     * @return string
     */
    public function getLevelNom($input=false){
        if($input)return $this->inputFormat($this->_user_level_nom);
        else return $this->outputFormat($this->_user_level_nom);
    }
    /**
     * renvoi l'identifiant de l'avatar
     * @return int
     */
    public function getMediaId(){
        return (isset($this->_media_id)&&$this->_media_id>0)?$this->_media_id:0;
    }
    /**
     * renvoi l'url vers l'avatar
     * @param boolean $blur
     * @param string $prefix
     * @return string
     * @todo revoir la partie blur...
     */
    public function getMediaUrl($blur=false,$prefix=''){
        $media = $this->getMedia();
        if($media->getUrl($blur,$prefix)===PWE::getBaseURL().PWE::getMissingMedia()){
            $fn=substr(PWE::getMissingMedia(),strrpos(PWE::getMissingMedia(), '/')+1);
            $media->setPath(str_replace($fn, 'membre_no_photo_'.$this->getSexe().'.jpg', PWE::getMissingMedia()));
        }
        return $media->getUrl($blur,$prefix);
    }
    /**
     * retourne l'avatar
     * @return \modele_media
     */
    public function getMedia(){
        if($this->_media===null){
            if($this->getMediaId()>0) $this->_media = new modele_media($this->_media_id);
            else $this->_media = new modele_media();
        }
        return $this->_media;
    }
    /**
     * indique si le membre a un avatar
     * @return boolean
     */
    public function haveMedia(){
        return ($this->getMediaId()>0)?true:false;
    }
    /**
     * ajoute un avatar
     * @param int $mediaId
     * @return boolean
     */
    public function addMedia($mediaId){
        $this->setDatas(array('_media_id'=>$mediaId));
        $this->_media = new modele_media($mediaId);
        $sqlQueryMedia = 'UPDATE '.self::getTable().' SET ';
        $sqlQueryMedia .= 'media_id='.$this->getMedia()->getId().' ';
        $sqlQueryMedia .= 'WHERE id='.$this->getId().' LIMIT 1';
        bdd_sql::doSql($sqlQueryMedia,false);
        return true;
    }
    /**
     * suprime l'avatar
     * @return boolean
     */
    public function removeMedia($mediaId=0){
        $mediaToDelete = $this->getMedia();
        $this->_media_id = 0;
        $this->_media = null;
        $sqlQueryMedia = 'UPDATE '.self::getTable().' SET ';
        $sqlQueryMedia .= 'media_id=NULL ';
        $sqlQueryMedia .= 'WHERE id='.$this->getId();
        bdd_sql::doSql($sqlQueryMedia,false);
        $mediaToDelete->delete();
        return true;
    }
    /**
     * retourne l'identifiant de l'objet adresse
     * @return int
     */
    public function getAdresseId(){
        return (isset($this->_adresse_id)&&$this->_adresse_id>0)?$this->_adresse_id:0;
    }
    /**
     * retourne l'objet modele_adresse
     * @return \modele_adresse
     */
    public function getAdresse(){
        if(isset($this->_adresse)&&$this->_adresse!==null){
            return $this->_adresse;
        }elseif($this->getAdresseId()>0){
            $this->_adresse = new modele_adresse($this->getAdresseId());
            return $this->_adresse;
        }else{
            return null;
        }
    }
    /**
     * retourne l'identifiant de région
     * @return int
     */
    public function getRegionId(){
        if($this->getAdresse()!=null){
            return $this->getAdresse()->getRegionId();
        }
        return 0;
    }
    /**
     * retourne l'objet modele_region
     * @return \modele_region|null
     */
    public function getRegion(){
        if($this->getRegionId()>0){
            return new modele_region($this->getRegionId());
        }
        return null;
    }
    /**
     * renvoi le tableau des propriétés du membre
     * @param boolean $input
     * @return array
     */
    public function getDatas($input=false) {
        $data = array();
        $data['id'] = $this->getId();
        $data['nom'] = $this->getNom($input);
        $data['prenom'] = $this->getPrenom($input);
        $data['sexe'] = $this->getSexe();
        $data['naissance'] = $this->getNaissance();
        $data['email'] = $this->getEmail();
        $data['phone1'] = $this->getPhone1();
        $data['phone2'] = $this->getPhone2();
        $data['level_id'] = $this->getLevelId();
        $data['level_nom'] = $this->getLevelNom($input);
        $data['level'] = $this->getLevel();
        $data['date_crea'] = $this->getDateCrea();
        $data['is_verif'] = $this->isVerif();
        $data['is_newsletter'] = $this->isNewsletter();
        $data['user_level_id'] = $this->getLevelId();
        $data['media'] = ($this->getMedia()!==null)?$this->getMedia():null;
        $data['adresse'] = ($this->getAdresse()!=null)?$this->getAdresse()->getDatas($input):null;
        $data['is_banned'] = $this->isBannded();
        $data['modele'] = 'user';
        
        return $data;
    }
    public function setDatas($datas) {
        foreach($datas as $k=>$v){
            if(substr($k, 0, 1) !== '_') $k = '_'.$k;
            if($k != '_id' && property_exists('modele_user',$k)){
                if($k==='_mdp'){
                    if($v!==''){
                        $this->$k = self::encodePW($v);
                    }
                }else{
                    $this->$k = $v;
                }
            }
        }
        //adresse
        $adresse = $this->getAdresse();
        
        if($adresse===null){
            $adresse = new modele_adresse();
        }
        $adresse->setDatas($datas);
        $this->_adresse = $adresse;
        
        //media
        if($this->getMedia()!=null)$this->getMedia()->setDatas($datas);
        
        return true;
    }
    /**
     * enregistre la date de dernière action
     */
    public function logLastConnection(){
        $sql = 'UPDATE '.self::getTable().' SET last_connect="'.date('Y-m-d H-i-s').'" WHERE id='.$this->getId();
        bdd_sql::doSql($sql,false);
    }
    public function getLastConnection(){
        $sql = 'SELECT last_connect FROM '.self::getTable().' WHERE id='.$this->getId();
        $result = bdd_sql::doSql($sql,true);
        return $result['results'][0]['last_connect'];
    }
    /**
     * inscription des utilisateurs
     * @return array
     */
    public function save(){ 
        if($this->getAdresse() !== null && $this->getAdresse()->getId()>0){
            $retour = $this->getAdresse()->update();
        }elseif($this->getAdresse() !== null){
            $retour = $this->getAdresse()->save();
        }
        if($retour['success']){
            $this->_adresse_id = $this->getAdresse()->getId();
        }
        
        // recuperation du niveau utilisateur
        $sqlLevel = 'SELECT * FROM '.PWE::getPrefixBdd().'user_level WHERE id='.$this->getLevelId().' LIMIT 1';
        $datas = $this->_getOneResult($sqlLevel);
        if($datas){
            $this->_user_level = $datas['level'];
            $this->_user_level_nom = $datas['nom'];
        }
        
        $retour = array('success'=>false,'datas'=>array());
        
        $this->_date_crea = date("Y-m-d H:i:s");
        
        $sqlQueryUser = 'INSERT INTO '.self::getTable().' ';
        $sqlQueryUser .= '(nom,prenom,sexe,naissance,adresse_id,user_level_id,is_verif,is_newsletter,email,phone1,phone2,mdp,date_crea) ';
        $sqlQueryUser .= 'VALUES (';
        $sqlQueryUser .= '"'.$this->getNom(true).'", ';
        $sqlQueryUser .= '"'.$this->getPrenom(true).'", ';
        $sqlQueryUser .= '"'.$this->getSexe().'", ';
        $sqlQueryUser .= '"'.$this->getNaissance().'", ';
        $sqlQueryUser .= ($this->getAdresseId()>0)?$this->getAdresseId():"null";
        $sqlQueryUser .= ', ';
        $sqlQueryUser .= $this->getLevelId().', '; // level membre
        $sqlQueryUser .= $this->_is_verif.', ';
        $sqlQueryUser .= $this->_is_newsletter.', ';
        $sqlQueryUser .= '"'.$this->getEmail().'", ';
        $sqlQueryUser .= '"'.$this->getPhone1().'", ';
        $sqlQueryUser .= '"'.$this->getPhone2().'", ';
        $sqlQueryUser .= '"'.$this->getPassword().'", ';
        $sqlQueryUser .= '"'.$this->_date_crea.'")';
        
        $result = bdd_sql::doSql($sqlQueryUser,false);
        if($result['count']>0){
            $this->setId(bdd_sql::getLastInsertId());
            $retour['success'] = true;
            $retour['datas'] = $this->getDatas();
        }else{
            if(stripos(bdd_sql::$LASTERROR,'duplicate entry')!==false){
                $retour['error'] = 'email existant';
            }else{
                $retour['error'] = bdd_sql::$LASTERROR;
            }
            $this->getAdresse()->delete();
        }
        return $retour;
    }
    public function update(){
        //adresse
        if($this->getAdresse() !== null && $this->getAdresse()->getId()>0){
            $retour = $this->getAdresse()->update();
        }elseif($this->getAdresse() !== null){
            $retour = $this->getAdresse()->save();
        }
        if($retour['success']){
            $this->_adresse_id = $this->getAdresse()->getId();
        }
        
        $sqlQueryUser = 'UPDATE '.self::getTable().' SET ';
        $sqlQueryUser .= 'nom="'.$this->getNom(true).'",';
        $sqlQueryUser .= 'prenom="'.$this->getPrenom(true).'",';
        $sqlQueryUser .= 'sexe="'.$this->getSexe().'",';
        $sqlQueryUser .= 'email="'.$this->getEmail().'",';
        $sqlQueryUser .= 'phone1="'.$this->getPhone1().'",';
        $sqlQueryUser .= 'phone2="'.$this->getPhone2().'",';
        $sqlQueryUser .= 'mdp="'.$this->getPassword().'",';
        $sqlQueryUser .= 'naissance="'.$this->getNaissance().'",';
        $sqlQueryUser .= 'adresse_id='.$this->_adresse_id.',';
        $sqlQueryUser .= 'is_verif='.$this->_is_verif.',';
        $sqlQueryUser .= 'is_banned='.$this->_is_banned.',';
        $sqlQueryUser .= 'is_deleted='.$this->_is_deleted.',';
        $sqlQueryUser .= 'is_newsletter='.$this->_is_newsletter.',';
        $sqlQueryUser .= 'user_level_id='.$this->getLevelId().' ';
        $sqlQueryUser .= 'WHERE id='.$this->_id.' LIMIT 1';
        
        bdd_sql::doSql($sqlQueryUser,false);
        
        //media
        if($this->getMedia()!=null)$this->getMedia()->update();

        return array('success'=>true,'datas'=>$this->getDatas());
    }
    /**
     * place le tag is_deleted à 1
     * @return array
     */
    public function delete() {
        $this->_is_deleted = 1;
        $sqlDelete = 'UPDATE '.self::getTable().' SET ';
        $sqlDelete .= 'is_deleted='.$this->_is_deleted.' ';
        $sqlDelete .= 'WHERE id='.$this->_id.' LIMIT 1';
        
        bdd_sql::doSql($sqlDelete,false);
        return array('success'=>true,'datas'=>array());
    }
    
    public function logout(){
        session::delete('user');
        session::deleteCookie('user_id', '/');
    }
    public function autoLogin(){
        session::set('user',  serialize($this));
    }
    public function logoutAdmin(){
        session::delete('admin');
        session::deleteCookie('admin_id', '/');
    }
    public function autoLoginAdmin(){
        if($this->getLevel()<2)session::set('admin',  serialize($this));
    }
    
    /* methodes statiques */
    /**
     * retourne le nom de la table prefixé
     * @return string
     */
    public static function getTable(){
        return PWE::getPrefixBdd().self::$_table;
    }
    /**
     * renvoi la liste des niveaux utilisateur disponibles
     * array['id','nom','level']
     * @return array
     */
    public static function getListLevel(){
        $sql = 'SELECT * FROM '.PWE::getPrefixBdd().'user_level ORDER BY id';
        $results = bdd_sql::doSql($sql,true,true);
        $retour = array();
        if($results['count']>0){
            $retour = $results['results'];
        }
        return $retour;
    }
    
    /**
     * renvoi la liste des user
     * @param int $start
     * @return \modele_user
     * @todo gestion des pagination $limit, $start
     */
    public static function getListe($start = 0){
        $sqlQuery = 'SELECT id FROM '.self::getTable().' ORDER BY id DESC';
        if($start>0){
            $sqlQuery .=  ''; // TODO pagination
        }
        $retour = array();
        $results = bdd_sql::doSql($sqlQuery,true);
        if($results['count']>0){
            foreach($results['results'] as $userData){
                $retour[] = new modele_user($userData['id']);
            }
        }
        return $retour;
    }
    /**
     * renvoi la liste des admin de niveau supérieur ou égal à $levelMin
     * @param int $levelMin
     * @return \modele_user
     */
    public static function getListeAdmin($levelMin = 0){
        $sqlQuery = 'SELECT u.id AS id FROM '.self::getTable().' AS u ';
        $sqlQuery .= 'LEFT JOIN '.PWE::getPrefixBdd().'user_level AS ul ON ul.id=u.user_level_id ';
        $sqlQuery .= 'WHERE ul.level>='.$levelMin.' AND ul.level<3 AND u.is_deleted=0 ';
        $sqlQuery .= 'ORDER BY id DESC';
        
        $retour = array();
        $results = bdd_sql::doSql($sqlQuery,true);
        if($results['count']>0){
            foreach($results['results'] as $userData){
                $retour[] = new modele_user($userData['id']);
            }
        }
        return $retour;
    }
    
    /**
     * retourne le nombre de membre selon les critères $params
     * @param array $params
     * @return int
     */
    public static function getNombreMembre($params=array()){
        if(isset($params['region_id']) && $params['region_id']>0){
            $sqlQuery = 'SELECT COUNT(u.id) AS qt FROM '.self::getTable().' AS u ';
            $sqlQuery .= 'LEFT JOIN '.PWE::getPrefixBdd().'user_level AS ul ON ul.id=u.user_level_id ';
            $sqlQuery .= 'LEFT JOIN '.modele_adresse::getTable().' AS ad ON ad.id=u.adresse_id ';
            $sqlQuery .= 'LEFT JOIN '.PWE::getPrefixBdd().'ville AS v ON v.id=ad.ville_id ';
            $sqlQuery .= 'LEFT JOIN '.modele_region::getTable().' AS r ON r.id=v.region_id ';
            $sqlQuery .= 'WHERE ul.level>2  AND u.is_deleted=0 ';
            $sqlQuery .= 'AND r.category_id='.$params['region_id'].' ';
            if(isset($params['with_media']) && $params['with_media']==1){
                $sqlQuery .= 'AND u.media_id>0';
            }
        }else{
            $sqlQuery = 'SELECT COUNT(u.id) AS qt FROM '.self::getTable().' AS u LEFT JOIN user_level AS ul ON ul.id=u.user_level_id WHERE ul.level>2 AND u.is_deleted=0 ORDER BY u.nom';
        }
        
        $retour = 0;
        $results = bdd_sql::doSql($sqlQuery,true);
        if($results['count']==1){
            $retour = $results['results'][0]['qt'];   
        }
        return $retour;
    }
    
    /**
     * retourne la liste des membres selon les critères params
     * @param array $params
     * @return \modele_user
     */
    public static function getSearchMembre($params,$start=0,$limit=0){
        $sqlQueryCount = 'SELECT COUNT(u.id) AS total';
        $sqlQueryResult = 'SELECT u.*,ad.adresse1 AS adresse1, ad.adresse2 AS adresse2, v.nom AS ville, v.cp AS cp ';
        $sqlQuery = '';
        
        
        $sqlQuery .= ' FROM '.self::getTable().' AS u';
        
        
        // jointures
        $sqlQuery .= ' LEFT JOIN user_level AS ul ON ul.id=u.user_level_id '; // pour trier les membres
		$sqlQuery .= ' LEFT JOIN '.modele_adresse::getTable().' AS ad ON ad.id=u.adresse_id ';
		$sqlQuery .= ' LEFT JOIN '.PWE::getPrefixBdd().'ville AS v ON v.id=ad.ville_id ';
		$sqlQuery .= ' LEFT JOIN '.modele_region::getTable().' AS r ON r.id=v.region_id ';
        
        //conditions
        $sqlQuery .= 'WHERE';
        if(isset($params['nom'])&&$params['nom']!='') $sqlQuery .= ' u.nom LIKE "'.self::inputFormat($params['nom']).'" AND';
        if(isset($params['email'])&&$params['email']!='') $sqlQuery .= ' u.email LIKE "'.$params['email'].'" AND';
        if(isset($params['sexe_h'])&&$params['sexe_h']=='1'&&(!isset($params['sexe_f'])||$params['sexe_f']!='1')) $sqlQuery .= ' u.sexe="h" AND';
        if(isset($params['sexe_f'])&&$params['sexe_f']=='1'&&(!isset($params['sexe_h'])||$params['sexe_h']!='1')) $sqlQuery .= ' u.sexe="f" AND';
        if(isset($params['region_id'])&&$params['region_id']>0) $sqlQuery .= ' r.category_id='.$params['region_id'].' AND';
        //dates
        if(isset($params['date_crea'])&&$params['date_crea']!='') $sqlQuery .= ' u.date_crea '.$params['sens_date_crea'].' "'.$params['date_crea'].'" AND';
        if(isset($params['date_connexion'])&&$params['date_connexion']!='') $sqlQuery .= ' u.last_connect '.$params['sens_date_connexion'].' "'.$params['date_connexion'].'" AND';
        
        // ne prendre que les membres et comptes non supprimés
        $sqlQuery .= ' ul.level>2 AND u.is_deleted=0';
        
        //group
        $sqlQuery .= ' GROUP BY u.id';
        
        //tri
        $sqlQuery .= ' ORDER BY u.nom';
                
        //limits
        if($limit>0){
            $sqlLimit = ' LIMIT '.$start.', '.$limit;
            $nbResult = $sqlQueryCount.$sqlQuery;
            $count = bdd_sql::doSql($nbResult,true);
            $total = $count['results'][0]['total'];
        }else{
            $sqlLimit = '';
            $total = 0;
        }
        
        $retour = array();
        
        $result = $sqlQueryResult.$sqlQuery.$sqlLimit;
                
        $results = bdd_sql::doSql($result,true,true);
        if($results['count']>0){
            if($total===0) $total=$results['count'];
            $retour = $results['results'];
        }
        
        return array( 'total'=>$total,'datas'=>$retour);
    }
    /**
     * renvoi la liste des membres
     * @param array $params
     * @param int $start
     * @param int $limit
     * @return \modele_user
     */
    public static function getListeMembre($params=array(),$start=0,$limit=0){
        if(isset($params['region_id']) && $params['region_id']>0){
            $sqlQuery = 'SELECT u.id FROM '.self::getTable().' AS u ';
            $sqlQuery .= 'LEFT JOIN '.PWE::getPrefixBdd().'user_level AS ul ON ul.id=u.user_level_id ';
            $sqlQuery .= 'LEFT JOIN '.modele_adresse::getTable().' AS ad ON ad.id=u.adresse_id ';
            $sqlQuery .= 'LEFT JOIN '.PWE::getPrefixBdd().'ville AS v ON v.id=ad.ville_id ';
            $sqlQuery .= 'LEFT JOIN '.modele_region::getTable().' AS r ON r.id=v.region_id ';
            $sqlQuery .= 'WHERE ul.level>2 AND u.is_deleted=0 ';
            $sqlQuery .= 'AND r.category_id='.$params['region_id'].' ';
            if(isset($params['with_media']) && $params['with_media']==1){
                $sqlQuery .= 'AND u.media_id>0 ';
            }
            $sqlQuery .= 'ORDER BY u.nom';
        }else{
            $sqlQuery = 'SELECT u.id FROM '.self::getTable().' AS u LEFT JOIN '.PWE::getPrefixBdd().'user_level AS ul ON ul.id=u.user_level_id WHERE ul.level>2  AND u.is_deleted=0 ORDER BY u.nom';
        }
        
        if($limit>0){
            $sqlQuery .= ' LIMIT '.$start.','.$limit;
        }
        $retour = array();
                
        $results = bdd_sql::doSql($sqlQuery,true);
        if($results['count']>0){
            foreach($results['results'] as $userData ){
                $retour[] = new modele_user($userData['id']);
            }
        }
        return $retour;
    }
    
    /**
     * indique si un membre est connecté
     * @return boolean
     */
    public static function isConnectedUser(){
        return (self::getConnectedUser()!=null)?true:false;
    }
    /**
     * renvoi le membre connecté
     * @return \modele_user|null
     */
    public static function getConnectedUser(){
        return self::login();
    }
    public static function findByEmail($email=''){
        $retour = null;
        $sqlQuery = 'SELECT u.id FROM '.self::getTable().' AS u WHERE u.email="'.$email.'" LIMIT 1';
        $results = bdd_sql::doSql($sqlQuery,true);
        if($results['count']>0){
            foreach($results['results'] as $userData ){
                $retour = new modele_user($userData['id']);
            }
        }
        return $retour;
    }

    /**
     * connexion d'un membre
     * @param string $email
     * @param string $pw
     * @return \modele_user|null
     */
    public static function login($email='',$pw=''){
        $user = null;
        if( session::get('user')!==null ){
            $user = unserialize(session::get('user'));
        }elseif(isset($_COOKIE) && isset($_COOKIE['user_id']) && $_COOKIE['user_id']>0){
            $user = new modele_user($_COOKIE['user_id']);
        }elseif($email!==''&&$pw!==''){            
            $encodedPW = self::encodePW($pw);
            $sql = 'SELECT id FROM '.self::getTable().' WHERE email="'.$email.'" AND mdp="'.$encodedPW.'"';
            
            $result = bdd_sql::doSql($sql, true);
            
            if($result['count']==1){
                $user = new modele_user($result['results'][0]['id']);
            }
        }
        
        if($user!=null && !$user->isBannded()){
            session::set('user',  serialize($user));
            session::setCookie('user_id',$user->getId(),time() + 7*24*60*60,'/');
            $user->logLastConnection();
        }else{
            session::delete('user');
            session::deleteCookie('user_id', '/');
        }
        return $user;
    }
    
    /**
     * indique si un admin est connecté
     * @return boolean
     */
    public static function isConnectedAdmin(){
        if(session::get('admin')!==null){
            return true;
        }elseif(isset($_COOKIE) && isset($_COOKIE['admin_id']) && $_COOKIE['admin_id']>0){
            return true;
        }else{
            return false;
        }
    }
    /**
     * renvoi l'admin connecté
     * @return \modele_user|null
     */
    public static function getConnectedAdmin(){
        if(self::isConnectedAdmin()){
            return self::loginAdmin();
        }else{
            return null;
        }
    }
    /**
     * connexion d'un admin
     * @param string $email
     * @param string $pw
     * @return \modele_user|null
     */
    public static function loginAdmin($email='',$pw=''){
        $user = null;
        if( session::get('admin')!==null ){
            $user = unserialize(session::get('admin'));
            session::setCookie('admin_id',$user->getId(),time() + 7*24*60*60,'/');
        }elseif(isset($_COOKIE) && isset($_COOKIE['admin_id']) && $_COOKIE['admin_id']>0){
            $idUser = $_COOKIE['admin_id'];
            $user = new modele_user($idUser);
            session::set('admin',  serialize($user));
            session::setCookie('admin_id',$user->getId(),time() + 7*24*60*60,'/');
        }else{
            $encodedPW = self::encodePW($pw);
            $sql = 'SELECT u.id FROM '.self::getTable().' AS u LEFT JOIN '.PWE::getPrefixBdd().'user_level AS ul ON ul.id=u.user_level_id WHERE email="'.$email.'" AND mdp="'.$encodedPW.'" AND ul.level<3 LIMIT 1';
            $result = bdd_sql::doSql($sql, true);
            
            if($result['count']>0){
                $idUser = $result['results'][0]['id'];
                $user = new modele_user($idUser);
                session::set('admin',  serialize($user));
            }
        }
        return $user;
    }
    /**
     * encodage du mot de pass
     * @param string $pw
     * @return string
     */
    public static function encodePW($pw){
        return crypt($pw, '$2a$06$pigxelwebengineencoder$');
    }
}
?>